<?php

namespace app\database\optimization;

use think\facade\Db;

/**
 * 数据库维护任务
 * 建议每天凌晨2点执行
 *
 * 使用方法：
 * 1. 命令行执行：php maintenance_task.php
 * 2. 定时任务：添加到crontab
 * 3. ThinkPHP命令：think optimize:db
 */
class MaintenanceTask
{
    private $logFile;

    public function __construct()
    {
        $this->logFile = runtime_path() . 'log/db_maintenance_' . date('Y-m-d') . '.log';
    }

    /**
     * 记录日志
     */
    private function log($message)
    {
        $time = date('Y-m-d H:i:s');
        $logMessage = "[{$time}] {$message}\n";
        file_put_contents($this->logFile, $logMessage, FILE_APPEND);
        echo $logMessage;
    }

    /**
     * 更新表统计信息
     * 优化器依赖统计信息来生成最优执行计划
     */
    public function updateStatistics()
    {
        $this->log("=== 开始更新表统计信息 ===");

        $tables = [
            'oa_crm_customer',
            'oa_crm_order',
            'oa_crm_followup',
            'oa_crm_contact',
            'oa_crm_contract',
            'admin',
            'department'
        ];

        foreach ($tables as $table) {
            try {
                Db::query("ANALYZE TABLE {$table}");
                $this->log("✓ 已更新表 {$table} 的统计信息");
            } catch (\Exception $e) {
                $this->log("✗ 更新表 {$table} 失败：" . $e->getMessage());
            }
        }

        $this->log("=== 表统计信息更新完成 ===\n");
    }

    /**
     * 检查索引碎片
     * 碎片率超过30%时建议优化
     */
    public function checkIndexFragmentation()
    {
        $this->log("=== 开始检查索引碎片 ===");

        $sql = "
            SELECT
                TABLE_NAME,
                TABLE_ROWS,
                DATA_LENGTH,
                INDEX_LENGTH,
                ROUND(((DATA_LENGTH + INDEX_LENGTH) - TABLE_ROWS * 512) / (DATA_LENGTH + INDEX_LENGTH), 2) AS FRAGMENT_RATIO
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            ORDER BY FRAGMENT_RATIO DESC
        ";

        $results = Db::query($sql);
        $highFragmentCount = 0;

        foreach ($results as $row) {
            if ($row['FRAGMENT_RATIO'] > 0.3) {
                $this->log("⚠️  警告：表 {$row['TABLE_NAME']} 碎片率 " . ($row['FRAGMENT_RATIO'] * 100) . "% 超过30%");
                $this->log("   建议执行：OPTIMIZE TABLE {$row['TABLE_NAME']}");
                $highFragmentCount++;
            } elseif ($row['FRAGMENT_RATIO'] > 0.1) {
                $this->log("ℹ️  信息：表 {$row['TABLE_NAME']} 碎片率 " . ($row['FRAGMENT_RATIO'] * 100) . "%");
            }
        }

        if ($highFragmentCount === 0) {
            $this->log("✓ 所有表碎片率正常，无需优化");
        } else {
            $this->log("共发现 {$highFragmentCount} 个表需要优化");
        }

        $this->log("=== 索引碎片检查完成 ===\n");
    }

    /**
     * 优化索引碎片
     * 仅在碎片率超过30%时执行
     */
    public function optimizeFragmentation()
    {
        $this->log("=== 开始优化索引碎片 ===");

        $sql = "
            SELECT
                TABLE_NAME,
                ROUND(((DATA_LENGTH + INDEX_LENGTH) - TABLE_ROWS * 512) / (DATA_LENGTH + INDEX_LENGTH), 2) AS FRAGMENT_RATIO
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            HAVING FRAGMENT_RATIO > 0.3
            ORDER BY FRAGMENT_RATIO DESC
        ";

        $results = Db::query($sql);

        if (empty($results)) {
            $this->log("✓ 没有需要优化的表");
            $this->log("=== 索引碎片优化完成 ===\n");
            return;
        }

        foreach ($results as $row) {
            try {
                $this->log("正在优化表 {$row['TABLE_NAME']}...");
                Db::query("OPTIMIZE TABLE {$row['TABLE_NAME']}");
                $this->log("✓ 表 {$row['TABLE_NAME']} 优化完成");
            } catch (\Exception $e) {
                $this->log("✗ 表 {$row['TABLE_NAME']} 优化失败：" . $e->getMessage());
            }
        }

        $this->log("=== 索引碎片优化完成 ===\n");
    }

    /**
     * 清理过期数据
     * - 清理慢查询日志（30天前）
     * - 清理软删除的数据（90天前）
     */
    public function cleanupExpiredData()
    {
        $this->log("=== 开始清理过期数据 ===");

        // 清理慢查询日志
        try {
            $result = Db::query("DELETE FROM mysql.slow_log WHERE start_time < DATE_SUB(NOW(), INTERVAL 30 DAY)");
            $this->log("✓ 已清理30天前的慢查询日志");
        } catch (\Exception $e) {
            $this->log("✗ 清理慢查询日志失败：" . $e->getMessage());
        }

        // 清理软删除的数据
        $tables = ['oa_crm_customer', 'oa_crm_order', 'oa_crm_followup', 'oa_crm_contact', 'oa_crm_contract'];
        $cutoffTime = time() - 90 * 86400; // 90天前

        foreach ($tables as $table) {
            try {
                $deleted = Db::table($table)
                    ->where('delete_time', '>', 0)
                    ->where('delete_time', '<', $cutoffTime)
                    ->delete(true);

                if ($deleted > 0) {
                    $this->log("✓ 表 {$table} 清理了 {$deleted} 条软删除记录");
                } else {
                    $this->log("ℹ️  表 {$table} 没有需要清理的记录");
                }
            } catch (\Exception $e) {
                $this->log("✗ 清理表 {$table} 失败：" . $e->getMessage());
            }
        }

        $this->log("=== 过期数据清理完成 ===\n");
    }

    /**
     * 检查未使用的索引
     * 长期未使用的索引可以考虑删除
     */
    public function checkUnusedIndexes()
    {
        $this->log("=== 开始检查未使用的索引 ===");

        $sql = "
            SELECT
                t.TABLE_NAME,
                s.INDEX_NAME,
                s.COLUMN_NAME,
                IFNULL(tio.COUNT_READ, 0) AS count_read
            FROM information_schema.TABLES t
            LEFT JOIN information_schema.STATISTICS s
                ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
                AND t.TABLE_NAME = s.TABLE_NAME
            LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage tio
                ON t.TABLE_SCHEMA = tio.object_schema
                AND t.TABLE_NAME = tio.object_name
                AND s.INDEX_NAME = tio.index_name
            WHERE t.TABLE_SCHEMA = DATABASE()
                AND s.INDEX_NAME IS NOT NULL
                AND s.INDEX_NAME != 'PRIMARY'
                AND (tio.COUNT_READ IS NULL OR tio.COUNT_READ = 0)
            ORDER BY t.TABLE_NAME, s.INDEX_NAME
        ";

        $results = Db::query($sql);

        if (empty($results)) {
            $this->log("✓ 所有索引都在使用中");
        } else {
            $this->log("发现 " . count($results) . " 个未使用的索引：");
            foreach ($results as $row) {
                $this->log("  - {$row['TABLE_NAME']}.{$row['INDEX_NAME']} ({$row['COLUMN_NAME']})");
            }
        }

        $this->log("=== 未使用索引检查完成 ===\n");
    }

    /**
     * 生成性能报告
     */
    public function generatePerformanceReport()
    {
        $this->log("=== 开始生成性能报告 ===");

        // 1. 表大小统计
        $this->log("\n--- 表大小统计 ---");
        $sql = "
            SELECT
                TABLE_NAME,
                TABLE_ROWS,
                ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb,
                ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb,
                ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) AS total_mb
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
            ORDER BY total_mb DESC
        ";

        $results = Db::query($sql);
        foreach ($results as $row) {
            $this->log(sprintf(
                "%s: 数据=%sMB, 索引=%sMB, 总计=%sMB, 行数=%s",
                $row['TABLE_NAME'],
                $row['data_mb'],
                $row['index_mb'],
                $row['total_mb'],
                $row['TABLE_ROWS']
            ));
        }

        // 2. 最近慢查询
        $this->log("\n--- 最近慢查询（Top 5） ---");
        $sql = "
            SELECT
                query_time,
                lock_time,
                rows_sent,
                rows_examined,
                LEFT(sql_text, 100) AS sql_text
            FROM mysql.slow_log
            ORDER BY query_time DESC
            LIMIT 5
        ";

        try {
            $results = Db::query($sql);
            if (empty($results)) {
                $this->log("没有慢查询记录");
            } else {
                foreach ($results as $row) {
                    $this->log(sprintf(
                        "执行时间=%s秒, 检查行数=%s, SQL=%s...",
                        $row['query_time'],
                        $row['rows_examined'],
                        $row['sql_text']
                    ));
                }
            }
        } catch (\Exception $e) {
            $this->log("无法获取慢查询信息：" . $e->getMessage());
        }

        $this->log("\n=== 性能报告生成完成 ===\n");
    }

    /**
     * 执行完整维护流程
     */
    public function runFullMaintenance()
    {
        $this->log("========== 开始数据库维护任务 ==========");
        $this->log("开始时间：" . date('Y-m-d H:i:s'));

        try {
            // 1. 更新统计信息
            $this->updateStatistics();

            // 2. 检查碎片
            $this->checkIndexFragmentation();

            // 3. 优化碎片（可选）
            // $this->optimizeFragmentation();

            // 4. 检查未使用索引
            $this->checkUnusedIndexes();

            // 5. 清理过期数据
            $this->cleanupExpiredData();

            // 6. 生成性能报告
            $this->generatePerformanceReport();

            $this->log("========== 数据库维护任务完成 ==========");
            $this->log("完成时间：" . date('Y-m-d H:i:s'));
        } catch (\Exception $e) {
            $this->log("✗ 维护任务执行失败：" . $e->getMessage());
            $this->log("错误详情：" . $e->getTraceAsString());
        }
    }
}

// 如果直接运行此脚本
if (php_sapi_name() === 'cli' && isset($argv[0]) && realpath($argv[0]) === __FILE__) {
    // 初始化应用
    require __DIR__ . '/../../../../vendor/autoload.php';
    $app = require_once __DIR__ . '/../../../../thinkphp/base.php';
    $app->initialize();

    // 创建维护任务实例
    $task = new MaintenanceTask();

    // 执行参数
    $action = $argv[1] ?? 'full';

    switch ($action) {
        case 'stats':
            $task->updateStatistics();
            break;
        case 'fragment':
            $task->checkIndexFragmentation();
            break;
        case 'optimize':
            $task->optimizeFragmentation();
            break;
        case 'cleanup':
            $task->cleanupExpiredData();
            break;
        case 'report':
            $task->generatePerformanceReport();
            break;
        case 'full':
        default:
            $task->runFullMaintenance();
            break;
    }
}
